package com.finance.console.dao;

import com.finance.console.entity.IncomeExpense;
import com.finance.console.storage.BaseDAO;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class IncomeExpenseDAO extends BaseDAO {

    // 按用户、类型和日期范围查询
    public List<IncomeExpense> findByUserIdAndTypeAndDate(
            int userId,
            String type,
            Date startDate,
            Date endDate
    ) {
        String sql = "SELECT * FROM income_expense WHERE user_id=? AND type=? AND time BETWEEN ? AND ?";
        List<IncomeExpense> list = new ArrayList<>();
        try {
            executeQuery(sql, userId, type, startDate, endDate);
            while (rs.next()) {
                IncomeExpense record = new IncomeExpense();
                record.setAmount(rs.getBigDecimal("amount"));
                record.setType(rs.getString("type"));
                record.setCategory(rs.getString("category"));
                list.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    // 插入收支记录
    public void insert(IncomeExpense record) {
        String sql = "INSERT INTO income_expense (user_id, amount, type, category, time) " +
                "VALUES (?, ?, ?, ?, ?)";
        executeUpdate(sql,
                record.getUserId(), // 需要在IncomeExpense中补充getUserId()
                record.getAmount(),
                record.getType(),
                record.getCategory(),
                new java.util.Date() // 使用当前时间
        );
    }

    // 按用户和时间范围查询
    public List<IncomeExpense> findByUserIdAndTime(int userId, Date startDate, Date endDate) {
        String sql = "SELECT * FROM income_expense WHERE user_id=? AND time BETWEEN ? AND ?";
        List<IncomeExpense> list = new ArrayList<>();
        try {
            executeQuery(sql, userId, startDate, endDate);
            while (rs.next()) {
                IncomeExpense record = new IncomeExpense();
                record.setAmount(rs.getBigDecimal("amount"));
                record.setType(rs.getString("type"));
                record.setCategory(rs.getString("category"));
                list.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    public BigDecimal sumExpenseByType(int userId, String type, Date startDate, Date endDate) {
        // SQL逻辑：查询指定用户、类型、日期范围内的支出总和
        String sql = "SELECT SUM(amount) AS total FROM income_expense " +
                "WHERE user_id=? AND category=? AND time BETWEEN ? AND ? AND type='EXPENSE'";
        try {
            executeQuery(sql, userId, type, startDate, endDate);
            if (rs.next()) {
                // 返回总和（如果为null，返回0）
                return rs.getBigDecimal("total") != null ? rs.getBigDecimal("total") : BigDecimal.ZERO;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(); // 确保资源关闭
        }
        return BigDecimal.ZERO; // 异常时返回0
    }
}